package com.css.fxfzaqbz.modules.data.repository.impl;


import com.css.fxfzaqbz.constants.YNEnum;
import com.css.fxfzaqbz.modules.data.entity.ImportantActivity;
import com.css.fxfzaqbz.modules.data.entity.SchoolData;
import com.css.fxfzaqbz.modules.data.queryParams.SchoolDataQueryParams;
import com.css.fxfzaqbz.modules.data.repository.SchoolDataRepository;
import com.css.fxfzaqbz.util.PlatformObjectUtils;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Pageable;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Repository
public class SchoolDataRepositoryImpl implements SchoolDataRepository {

    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

//    @Override
//    @Transactional(rollbackFor = Exception.class)
//    public SchoolData updateSchoolData(SchoolData schoolData) {
//
//        String sql = "update sys_activefaultdata set afd_number = :afdNumber," +
//                " afd_name = :afdName, afd_length = :afdLength," +
//                " afd_newactivity = :afdNewactivity, afd_status = :afdStatus, " +
//                " afd_updatetime = :afdUpdatetime" +
//                " where afd_id = '"+activeFaultData.getAfdId()+"'" ;
//
//        SqlParameterSource source = new BeanPropertySqlParameterSource(activeFaultData);
//        //拿到新插入的主键值（i）
//        KeyHolder keyHolder = new GeneratedKeyHolder();
//        namedParameterJdbcTemplate.update(sql,source,keyHolder);
//        return activeFaultData;
//    }

    @Override
    @Transactional
    public SchoolData saveSchoolData(SchoolData schoolData) {
        String sql = "insert into sys_school_data (sd_Id,sd_number,sd_longitude,sd_latitude,sd_name,sd_type,sd_stunumber,sd_boarding,del_flag,create_time,update_time)values(:sdId,:sdNumber,:sdLongitude,:sdLatitude,:sdName,:sdType,:sdStunumber,:sdBoarding,:delFlag,:createTime,:updateTime)";

        SqlParameterSource source = new BeanPropertySqlParameterSource(schoolData);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return schoolData;
    }

    @Override
    public Map<String,Object> getAllSchoolDataParams(SchoolDataQueryParams queryParams) {
        StringBuilder sql = new StringBuilder(" select  sc.* from sys_school_data sc ");

        StringBuilder orderSql = new StringBuilder(" order by sc.sd_number");

        StringBuilder whereSql = new StringBuilder(" where sc.del_flag = '" + YNEnum.N.toString() + "'");

//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationBand()) ){
//            whereSql.append(" and e.elevation_band like '%" + queryParams.getElevationBand() + "%'");
//        }
//
//        if (!PlatformObjectUtils.isEmpty(queryParams.getElevationNodatavalue()) ){
//           whereSql.append(" and e.elevation_nodatavalue like '%" + queryParams.getElevationNodatavalue() + "%'");
//        }

        int firstIndex=(queryParams.getCurPage()-1) * queryParams.getPageSize();
        int lastIndex=queryParams.getCurPage() * queryParams.getPageSize();
        sql.append(whereSql).append(orderSql).toString();
        StringBuilder limitSql = new StringBuilder(" limit "+lastIndex+" offset "+ firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(SchoolData.class)).size();
        List<SchoolData> schoolData = jdbcTemplate.query(sql.append(limitSql).toString(), new BeanPropertyRowMapper<>(SchoolData.class));
        Map<String,Object> map=new HashMap();
        map.put("total",total);
        map.put("rows",schoolData);
        return map;
    }

    @Override
    public SchoolData getSchoolDataById(String id) {
        String sql = "select * from sys_school_data where sd_id = '"+id+"'  and del_flag = '"+YNEnum.N+"'";
        SchoolData schoolData = jdbcTemplate.queryForObject(sql, new BeanPropertyRowMapper<>(SchoolData.class));
        return schoolData;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public void deleteSchoolDatasById(List<String> ids, Timestamp timestamp,String userId) {
        String sql = "update sys_school_data set del_flag = '" + YNEnum.Y + "' , update_time = '"+timestamp+"' ,update_user = '"+userId+"'" + " where sd_id in (:ids)";
        Map map = new HashMap();
        map.put("ids", ids);
        namedParameterJdbcTemplate.update(sql, map);
    }

    @Override
    public List<SchoolData> getAll() {
        return jdbcTemplate.query("select * from sys_school_data where del_flag = '"+YNEnum.N+"'",new BeanPropertyRowMapper<>(SchoolData.class));
    }

    @Override
    public void importExcel(List<SchoolData> schoolData) {
        jdbcTemplate.batchUpdate("insert into sys_school_data (sd_id,sd_number,sd_longitude,sd_latitude,sd_name,sd_type,sd_stunumber,sd_boarding,sd_status,sd_createtime)values(?,?,?,?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public int getBatchSize() {
                return schoolData.size();
            }
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setString(1, schoolData.get(i).getSdId());
                ps.setString(2,schoolData.get(i).getSdNumber());
                ps.setFloat(3,schoolData.get(i).getSdLongitude());
                ps.setFloat(4,schoolData.get(i).getSdLatitude());
                ps.setString(5,schoolData.get(i).getSdName());
                ps.setString(6,schoolData.get(i).getSdType());
                ps.setInt(7,schoolData.get(i).getSdStunumber());
                ps.setString(8,schoolData.get(i).getSdBoarding());
                ps.setString(9,schoolData.get(i).getDelFlag());
                java.sql.Date createtime = new java.sql.Date(schoolData.get(i).getCreateTime().getTime()) ;
                ps.setDate(10,createtime);
            }
        });
    }

}
